<?php
require 'lib.php';

$server = "172.21.147.31";
$connectionInfo = array( "Database"=>"ss2g6", "UID"=>"ss2g6", "PWD"=>"group6", 'ReturnDatesAsStrings'=> true );
$conn = sqlsrv_connect( $server, $connectionInfo );

if ($conn == false){
	 die( print_r( sqlsrv_errors(), true));
}

$params = array();
$options =  array( "Scrollable" => SQLSRV_CURSOR_KEYSET );

/* Update GET */
if(isset($_GET['update'])){
    $updateDoctorId = $_GET['doctorId'];
    $sql = "SELECT * FROM Doctor WHERE doctorId = $updateDoctorId";
    $result = sqlsrv_query($conn, $sql, $params, $options);
    $row = sqlsrv_fetch_array( $result, SQLSRV_FETCH_ASSOC);

    $doctorName = $row['name'];
    $doctorExp = $row['yearsOfExperience'];
    $doctorSpecialty = $row['specialty'];
}

/* Update POST */
if (isset($_POST['Update'])){
    $updateDoctorId = $_POST['doctorId'];
    $doctorName = $_POST['name'];
    $doctorExp = $_POST['yearsOfExperience'];
    $doctorSpecialty = $_POST['specialty'];
    
    /*check that the text fields is not empty, if yearsOfExperience is empty, value 0 is added*/
	if( $doctorName == ""){
		$validation_error_1 = true;
	}
	else {
		$validation_error_1 = false;
	}
	
	/* check that the yearOfExperience is a integer*/
	if(is_numeric($doctorExp)){
		$numeric_1 = true;
	}
	else {
		$numeric_1 = false;
    }    

    if ($validation_error_1 || !$numeric_1){
        $update_error = true;
    }

    if (!isset($update_error)){
    	if(trim($doctorSpecialty) == "" || strtolower(trim($doctorSpecialty)) == "null"){
        	$sql = "UPDATE Doctor SET name='$doctorName', yearsOfExperience = $doctorExp, specialty = NULL WHERE doctorId = $updateDoctorId";
    	}
    	else{
    		$sql = "UPDATE Doctor SET name='$doctorName', yearsOfExperience = $doctorExp, specialty ='$doctorSpecialty' WHERE doctorId = $updateDoctorId";
    	}        	
        
        $result = sqlsrv_query($conn, $sql, $params, $options);	   
   		if($result === false){
            if(($errors = sqlsrv_errors()) != null){
                $error_msg = "";
                foreach($errors as $error){
                    $error_msg = $error_msg . " " . cut_string_using_last(']', $error['message'], 'right', false);
                }
            }
        }
	    else {
           $confirm = "The doctor information is updated.";;
	    }
    }
    else{
    	$confirm = "";
    }
}

if(isset($_POST['Insert'])) {
    $yearsOfExperience = $_POST['yearsOfExperience'];
    $name = $_POST['name'];
    $specialty = $_POST['specialty'];
	
	/*check that the text fields is not empty, if yearsOfExperience is empty, value 0 is added*/
	if( $name == ""){
		$validation_error = true;
	}
	else {
		$validation_error = false;
	}
	
	/* check that the yearOfExperience is a integer*/
	if(is_numeric($yearsOfExperience)){
		$numeric = true;
	}
	else {
		$numeric = false;
	}
	
    if($specialty == ""){
         $sql = "INSERT INTO Doctor VALUES ('$yearsOfExperience', '$name', NULL)";
    }
    else {
         $sql = "INSERT INTO Doctor VALUES ('$yearsOfExperience', '$name', '$specialty')";
    }
   if($numeric == true && $validation_error == false){
		$params = array();
		$options =  array( "Scrollable" => SQLSRV_CURSOR_KEYSET );
		$result = sqlsrv_query( $conn, $sql, $params, $options );
		
  		if($result === false){
            if(($errors = sqlsrv_errors()) != null){
                $error_msg = "";
                foreach($errors as $error){
                    $error_msg = $error_msg . " " . cut_string_using_last(']', $error['message'], 'right', false);
                }
            }
        }
	    else {
           $confirm = "The doctor information is inserted.";;
	    }
		
	}
	else {
		$confirm = "";
	}
}

if(isset($_GET['delete'])){
	$doctorId = $_GET['doctorId'];
	$sql = "DELETE FROM Doctor WHERE doctorId = '". $doctorId ."'";
    $result = sqlsrv_query( $conn, $sql, $params );
    if ($result === false){
        if(($errors = sqlsrv_errors()) != null){
            $error_msg = "";
            foreach($errors as $error){
                $error_msg = $error_msg . " " . cut_string_using_last(']', $error['message'], 'right', false);
            }
        }
    }else{
        $rowsAffected = sqlsrv_rows_affected ($result);
	
	    if ($rowsAffected === false){
		     $error = true;     
	    }
	    else{
		    $error = false;
	    }
   }
}

if (isset($_POST['submit'])){
    $sql1 = $_POST['query'];
}
else
{
    $sql = "SELECT * FROM Doctor";
}
$params = array();
$options =  array( "Scrollable" => SQLSRV_CURSOR_KEYSET );
$stmt = sqlsrv_query( $conn, $sql , $params, $options );
?>


<? include 'html_head.php' ?>
    <? include 'header.php' ?>

    <div class="container-fluid">
      <div class="row-fluid">
        <div class="span2">
			<? include 'sidebar.php' ?>
        </div><!--/span-->
        <div class="span9">
	    <h2>Doctors</h2>	
	    <hr>	
        <?
        /* Print Trigger errors if any first, then a general error message */
		if(isset($_GET['delete'])){
            if (isset($error_msg)){ ?>
				<div class="alert alert-error">
					<? echo $error_msg; ?>
				</div>
            <?
            }
            elseif ($error){ ?>
				<div class="alert alert-error">
                    <? echo "Unable to delete."; ?>
				</div>
				
			<? 
			} 
			else { ?>
				<div class="alert alert-success">
				<? echo "Data deleted successfully."?>
				</div>
				
			<?
			} 
		}?>
		
		<?
		if(isset($_POST['Update'])){
			$msg = "";
			if($numeric_1 == false){ 
				$msg = "Years of Experience text field must be an integer value <br />";
			}
			if($validation_error_1){
				$msg = $msg . "Name of a doctor cannot be empty. <br />";
			}
			if(isset($error_msg)){
				$msg = "Data cannot be updated, foreign key constraints or triggers violated <br />";
			}
			
			if ($msg <> ""){ ?>
				<div class="alert alert-error">
					<? echo $msg; ?>
				</div>		
			<? 
			} 
			elseif(isset($error_msg)){ ?>
			   	<div class="alert alert-error">
			    	<? echo $error_msg; ?>
				</div>
        	<? }else{ ?>
			   	<div class="alert alert-success">
			    	<? echo $confirm; ?>
				</div>
        	<?}?>		
		<?}?>
		
		
		<?
		if(isset($_POST['Insert'])){
			$msg = "";
			if($numeric == false){ 
				$msg = "Years of Experience text field must be an integer value <br />";
			}
			if($validation_error){
				$msg = $msg . "Name of a doctor cannot be empty. <br />";
			}
			if(isset($error_msg)){
				$msg = "Data cannot be inserted, foreign key constraints or triggers violated <br />";
			}
			
			if ($msg <> ""){ ?>
				<div class="alert alert-error">
					<? echo $msg; ?>
				</div>		
			<? 
			} 
			elseif(isset($error_msg)){ ?>
			   	<div class="alert alert-error">
			    	<? echo $error_msg; ?>
				</div>
        	<? }else{ ?>
			   	<div class="alert alert-success">
			    	<? echo $confirm; ?>
				</div>
        	<?}?>		
		<?}?>
		<?
        
		if(sqlsrv_has_rows($stmt))
		{		
            $row_count = sqlsrv_num_rows($stmt);
            $colnames = sqlsrv_field_metadata($stmt)
		?>
		
		<table class="table table-striped table table-condensed">
		
		<thead>
			<tr>
			   <?
                    echo "<th>Doctor ID</th>";
					echo "<th>Years of Experience</th>";
					echo "<th>Name</th>";
					echo "<th>Specialty</th>";
                ?>
			</tr>	
			</thead>
		<tbody>
		<?
			while( $row = sqlsrv_fetch_array( $stmt, SQLSRV_FETCH_ASSOC)){
		?>
			<tr>
				<?
					echo "<td>" . $row['doctorId'] . "</td>";
					echo "<td>" . $row['yearsOfExperience'] . "</td>";
					echo "<td>" . $row['name'] . "</td>";
					echo "<td>";
					if ($row['specialty'] == ""){
						echo "No specialty";
					}
					else{
						echo $row['specialty'];
					}
					echo "</td>";
					$doctorId = urlencode($row['doctorId']);
					echo "<td><a href='doctors.php?delete=true&doctorId=" . $doctorId . "'>Delete</a></td>";
					echo "<td><a href='doctors.php?update=true&doctorId=" . $doctorId . "#updates'>Update</a></td>";
				?>
			</tr>
		    
		<?
			}
		?>

		</tbody>
		</table>
		<?
		}
		else{
			echo "<h2>No records.</h2>";
		}
		?>
          <div class="row-fluid">
            <div class="span12">

            <!-- Update, appears only if update or update_error is set -->
            <? if (isset($_GET['update']) || isset($update_error)){ ?>
            <div>
                <a name="updates"></a><h3>Update Doctor information:</h3> 
                <form method="post" action="doctors.php" class="well form-horizontal">                    
                    <div class="control-group">
                    	<label class="control-label">Doctor ID</label>
                    	<div class="controls"><input type="text" name="doctorId" value="<? echo $updateDoctorId ?>" readonly /></div>
                    </div>
                    <div class="control-group">
                        <label class="control-label">Years Of Experience</label>
                        <div class="controls">
                            <input type="text" name="yearsOfExperience" id="yearsOfExperience" value="<? echo $doctorExp ?>" /> 
                        </div>
                    </div>
                    <div class="control-group">
                        <label class="control-label">Name</label>
                        <div class="controls">
                            <input type="text" name="name" id="name" value="<? echo $doctorName ?>"/>
                        </div> 
                    </div>
                    <div class="control-group">
                        <label class="control-label">Specialty</label>
                        <div class="controls">
                            <input type="text" name="specialty" id="specialty" value="<? echo $doctorSpecialty ?>"/>
                        </div>
                    </div>
                    <div class="form-actions">
                        <input type="submit" name="Update" value="Update"  class="btn btn-primary"/>
                    </div> 
                    </form>

            </div>
            <? } ?>
            <h3>Create a new Doctor information:</h3> 
                    <form method="post" action="doctors.php" class="well form-horizontal">
                    <div class="control-group">
                        <label class="control-label">Years Of Experience</label>
                        <div class="controls">
                            <input type="text" name="yearsOfExperience" id="yearsOfExperience"  placeholder="eg. 5"/>
                        </div>
                    </div>
                    <div class="control-group">
                        <label class="control-label">Name</label>
                        <div class="controls">
                            <input type="text" name="name" id="name" placeholder="eg. Linda Chong" />
                        </div> 
                    </div>
                    <div class="control-group">
                        <label class="control-label">Specialty</label>
                        <div class="controls">
                            <input type="text" name="specialty" id="specialty" placeholder="eg. Neurology" />
                        </div>
                    </div>
                    <div class="form-actions">
                        <input type="submit" name="Insert" value="Insert"  class="btn btn-primary"/>
                    </div> 
                    </form> 

               <h3>Enter your query:</h3>
                    <form method="post" action="custom.php" class="well form-inline">
                    <div class="control-group">
                        <div class="controls">
                            <textarea name="query" class="span10" id="textarea" rows="5" placeholder = 'SQL query'></textarea>
                        </div>
                    </div>
                    <p>
                    <p><input type="submit" name="submit" class="btn btn-primary" href="#" /></p>
                    </form>
                    
            </div><!--/span-->
          </div><!--/row-->
        </div><!--/span-->
      </div><!--/row-->

      <hr>

      <footer>
        <p>&copy; SS2 GROUP 6 </p>
      </footer>

    </div><!--/.fluid-container-->
  </body>
</html>
